package com.openbravo.pos.htutils;

import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;

/*
 * Database Related Tools
 */
public class DBTools {

    String udbUsername = "root";
    String dbPassword = "admin";
    boolean dbExists = false;
    Connection connection;// = null;

    public String getReturnedMessage(String transNumber) {
        try {
            String ReturnedMessage = null;
            String url = "jdbc:mysql://localhost/opos_dev";
            Connection conn = DriverManager.getConnection(url, udbUsername, dbPassword);
            Statement stmt = conn.createStatement();
            ResultSet rs;
            try {
                rs = stmt.executeQuery("SELECT RETURNMSG FROM PAYMENTS WHERE TRANSID ='" + transNumber + "';");
                while (rs.next()) {
                    ReturnedMessage = rs.getString("returnmsg");
                }
                return ReturnedMessage;
            } catch (SQLException ex) {
                Logger.getLogger(DBTools.class.getName()).log(Level.SEVERE, null, ex);
            } finally {
                try {
                    conn.close();
                } catch (SQLException ex) {
                    Logger.getLogger(DBTools.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
            return ReturnedMessage;
        } catch (SQLException ex) {
            Logger.getLogger(DBTools.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    /*
     * Creates a new database if it does not already exists.
     */
    public void createDatabase() {

        try {

            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://localhost/", udbUsername, dbPassword);

            try {
                connection = DriverManager.getConnection("jdbc:mysql://localhost/opos_dev", udbUsername, dbPassword);
                dbExists = true;
            } catch (Exception e) {
                dbExists = false;
            }

            if (!dbExists) {
                try {
                    Statement stmt = connection.createStatement();
                    Statement stmtUser = connection.createStatement();

                    connection.setAutoCommit(false);
                    String dbName = "opos_dev";
                    String createdatabasequery = "Create Database " + dbName;
                    stmt.addBatch(createdatabasequery);
                    stmt.executeBatch();
                    connection.commit();


                    //Create new user, just in case the root access is not working.
                    String createUser = "CREATE USER 'posuser'@'localhost' IDENTIFIED BY 'posuser';";
                    String GivePermission = "GRANT ALL ON *.* TO 'posuser'@'localhost';";
                    //String changeRoot = "update user set password=PASSWORD('') where User='root'";

                    stmtUser.addBatch(createUser);
                    stmtUser.addBatch(GivePermission);
                    //stmtUser.addBatch(changeRoot);
                    stmtUser.executeBatch();
                    connection.commit();

                } catch (SQLException s) {
                    System.out.println("SQL Exception: " + s);
                }
            }

        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e);

            // TODO: Database not installed, proceed with DB Installation: 
            // TODO: Done with installer

            // Return back to above steps.
            // e.printStackTrace();

        }
    }
}
